package com.ce.pms.house.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.annotation.AssociatedBuildingQuery;
import com.ce.pms.common.model.SysInfo;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.common.util.StringAnalysiUtil;
import com.ce.pms.customer.model.ClientRHouseDO;
import com.ce.pms.customer.model.IndividualDO;
import com.ce.pms.customer.model.OrganizationDO;
import com.ce.pms.exception.BusinessException;
import com.ce.pms.exception.DataAccessLayerException;
import com.ce.pms.house.dao.IHouseDao;
import com.ce.pms.house.model.CoalitionHouseDomain;
import com.ce.pms.house.model.FlatDomain;
import com.ce.pms.house.model.HouseDomain;
import com.ce.pms.house.model.HousePlan;
import com.ce.pms.house.model.HouseType;
import com.ce.pms.house.vo.HouseSearchVO;
import com.ce.util.BlankUtil;
import com.sitechasia.webx.core.support.Page;

public class HouseDaoImpl extends PmsBaseDaoImpl<HouseDomain> implements
		IHouseDao {

	private static String HOUSE_QUERY_SQL_CONFIGPATH = "House_HouseManagerSQL.xml";

	private static final String QUERY_PRORIGHTH_OUSEID_STR_BYID = "queryProRightHouseIdStrById";

	/**
	 *
	 * 功能描述: 检查指定楼盘期数下的id不是houseId的指定房号的产权房是否存在
	 *
	 * @author 付湘辉 Created on:2008-7-1
	 *
	 * 修改原因：
	 *
	 * @param projectId
	 * @param houseId
	 * @param houseNumber
	 *
	 * @return boolean true 存在 false 不存在
	 */
	public boolean checkIsOnlyNumber(Long projectId, Long houseId,
			String houseNumber) {
		return this.getHouseDomainBy(projectId, houseId, houseNumber, false) != null;
	}

	/**
	 *
	 * 功能描述: 检查指定楼盘期数下的指定房号的产权房是否存在
	 *
	 * @author 付湘辉 Created on:2008-7-1
	 *
	 * 修改原因：
	 *
	 * @param projectId
	 * @param houseNumber
	 * @return boolean true 存在 false 不存在
	 */
	public boolean checkIsOnlyNumber(Long projectId, String houseNumber) {
		return this.getHouseDomainByHouseNumber(projectId, houseNumber) != null;
	}

	/**
	 *
	 * 功能描述: 检查指定房号的产权房是否存在
	 *
	 * @author 付湘辉 Created on:2008-7-1
	 *
	 * 修改原因：
	 *
	 * @param projectId
	 * @param houseNumber
	 * @return boolean true 存在 false 不存在
	 */
	public boolean checkIsOnlyNumber(String houseNumber) {
		return this.getHouseDomainByHouseNumber(houseNumber) != null;
	}

	/**
	 * 功能描述: 根据指定的房屋ID集合查询楼栋的数量 <br>
	 *
	 * @param houseIdStr
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-7-31 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	public Integer getBuildingCountBy(String houseIdStr) {
		String sql = "SELECT COUNT(DISTINCT HMH.BUILDING_ID) FROM HM_HOUSE HMH WHERE HMH.HOUSE_ID IN (:houseIdStr) AND HMH.IS_VIRTUAL=0 ";
		sql = sql.replace(":houseIdStr", houseIdStr);
		Query query = super.createSQLQuery(sql);
		Object obj = query.uniqueResult();
		Integer result = ((BigInteger) obj).intValue();
		return result;
	}

	/**
	 * 功能描述:查找指定房号的所有业主<br>
	 *
	 * @param houseId
	 * @return<br>
	 *         Create author:罗军林<br>
	 *         Create on:2008-10-14<br>
	 *         Edit author:<br>
	 *         Edit on:<br>
	 *         Why:<br>
	 */
	public String getClientsByHouseId(Long houseId) {
		String sql = "SELECT CLIENTS FROM V_FEE_HOUSE_CLIENTS WHERE HOUSE_ID=?";
		Query query = createSQLQuery(sql, houseId);
		return (String) query.uniqueResult();
	}

	/**
	 *
	 * 功能描述: 获取指定产权房屋的管理房实体 <br>
	 *
	 * @param houseDomain
	 * @return
	 *
	 * Create author：付湘辉 <br>
	 * Create on:2008-7-25 <br>
	 * Edit author： <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	public CoalitionHouseDomain getCoalition(HouseDomain houseDomain) {
		String hql = "SELECT ct FROM CoalitionHouseDomain ct inner join ct.houses h where h = ? AND ct.validity=1 ";

		Object obj = createQuery(hql, houseDomain).uniqueResult();

		return obj == null ? null : (CoalitionHouseDomain) obj;
	}

	/**
	 *
	 * 功能描述: 获取指定产权房屋的管理房实体 <br>
	 *
	 * @param houseDomain
	 * @return
	 *
	 * Create author：付湘辉 <br>
	 * Create on:2008-7-25 <br>
	 * Edit author： <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	public CoalitionHouseDomain getCoalition(Long houseId) {
		HouseDomain house = super.findById(houseId);
		return this.getCoalition(house);
	}

	/**
	 * 功能描述: 查询指定了房屋id的当前所使用的账户所在的房屋id <br>
	 * 当记账策略是合并记账时，若是指定的id是产权房id，则返回其管理房id，否则返回本身， 若是指定的id是管理房id，则返回本身 <br>
	 *
	 * @param houseId
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-11-4 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	public Long getEffectiveAccountHouseId(Long houseId) {

		StringBuffer sb = new StringBuffer("SELECT ");
		sb
				.append(" (CASE")
				.append(" WHEN HM.IS_VIRTUAL=0")
				.append(
						" THEN IF(HP.CHARGE_SELECT='0',HMA.MANAGE_ID,HM.HOUSE_ID)")
				.append(" ELSE HM.HOUSE_ID END) CURHOUSEID")
				.append(" FROM HM_HOUSE HM")
				.append(" JOIN HM_PRO_RIGHT HP ON HM.HOUSE_ID = HP.HOUSE_ID")
				.append(
						" LEFT JOIN HM_REAL_R_VIRTUAL HRRV ON HM.HOUSE_ID = HRRV.REAL_HOUSE_ID")
				.append(
						" LEFT JOIN HM_MANAGE HMA ON HRRV.VIRTUAL_HOUSE_ID = HMA.MANAGE_ID")
				.append(" WHERE HM.HOUSE_ID=? AND HP.VALIDITY=1 ");

		Query query = super.createSQLQuery(sb.toString(), houseId);
		List list = query.list();
		if (BlankUtil.isBlank(list)) {
			return null;
		}
		return ((BigInteger) list.get(0)).longValue();
	}

	/**
	 * 功能描述: 查询指定楼栋的房屋总套数 <br>
	 *
	 * @param buildingId
	 * @return Integer
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-7-17 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	public Integer getHouseCount(Long buildingId) {
		Integer result = this.getHouseNumberBy(buildingId, null);
		return result;
	}

	/**
	 *
	 * 功能描述: 查询指定楼盘期数下的该房号是否存在,存在返回HouseDomain,否则返回null
	 *
	 * @author 付湘辉 Created on:2008-7-11
	 *
	 * 修改原因：
	 * @param projectId
	 * @param houseNumber
	 * @return HouseDomain 返回结果
	 */
	public HouseDomain getHouseDomainByHouseNumber(Long projectId,
			String houseNumber) {
		HouseDomain house = this.getHouseDomainBy(projectId, null, houseNumber,
				false);
		return house;
	}

	/**
	 *
	 * 功能描述: 查询指定房号的产权房,存在返回HouseDomain,否则返回null
	 *
	 * @author 付湘辉 Created on:2008-7-11
	 *
	 * 修改原因：
	 * @param houseNumber
	 * @return HouseDomain 返回结果
	 */
	public HouseDomain getHouseDomainByHouseNumber(String houseNumber) {
		return this.getHouseDomainByHouseNumber(houseNumber, false);
	}

	/**
	 *
	 * 功能描述: 查询指定房号的房间（查询范围包括管理房）,存在返回HouseDomain,否则返回null
	 *
	 * @author 付湘辉 Created on:2008-7-11
	 *
	 * 修改原因：
	 * @param houseNumber
	 * @param virtual
	 *            是否查询管理房 true：是 false：否
	 * @return HouseDomain 返回结果
	 */
	public HouseDomain getHouseDomainByHouseNumber(String houseNumber,
			boolean virtual) {
		HouseDomain house = this.getHouseDomainBy(null, null, houseNumber,
				virtual);
		return house;
	}

	/**
	 * 功能描述:根据房间号模糊查询出其房间ID，如是管理房则是其下的产权房ID。返回值为id串，如1,2,3<br>
	 *
	 * @param houseNumber
	 * @return<br>
	 *         Create author:罗军林<br>
	 *         Create on:2008-10-15<br>
	 *         Edit author:<br>
	 *         Edit on:<br>
	 *         Why:<br>
	 */
	public String getHouseIdByNumber(String houseNumber) {
		StringBuffer houseNumberLike = new StringBuffer();
		houseNumberLike.append("%").append(houseNumber).append("%");

		StringBuffer sql = new StringBuffer();
		sql
				.append(
						" SELECT  GROUP_CONCAT(CAST(  X.HOUSE_ID   AS CHAR) SEPARATOR ',') AS HOUSEIDS ")
				.append(" FROM  ")
				.append(" ( ")
				.append(" SELECT ")
				.append(" '1' AS GG, ")
				.append(" R.REAL_HOUSE_ID  AS HOUSE_ID ")
				.append(" FROM HM_HOUSE H ")
				.append(
						" JOIN HM_REAL_R_VIRTUAL R ON R.VIRTUAL_HOUSE_ID = H.HOUSE_ID ")
				.append(
						" JOIN HM_MANAGE AS M ON M.MANAGE_ID = R.VIRTUAL_HOUSE_ID ")
				.append(" WHERE  ").append(" H.HOUSE_NUMBER LIKE  ? ").append(
						" AND  ").append(" H.IS_VIRTUAL='1' ").append(
						" AND M.VALIDITY='1' ").append(" UNION ").append(
						" SELECT  ").append(" '1' AS GG, ").append(
						" H.HOUSE_ID  AS HOUSE_ID ")
				.append(" FROM HM_HOUSE H ").append(" WHERE  ").append(
						" H.HOUSE_NUMBER LIKE ? ").append(" AND  ").append(
						" H.IS_VIRTUAL=0 ").append(" ) X ").append(
						" GROUP BY GG ");

		Query query = createSQLQuery(sql.toString(),
				houseNumberLike.toString(), houseNumberLike.toString());

		return (String) query.uniqueResult();
	}

	/**
	 *
	 * 功能描述: 根据楼栋id查询其下所有房屋的id列表信息
	 *
	 * @author 付湘辉 Created on:2008-7-8
	 *
	 * 修改原因：
	 * @param buildingId
	 * @return
	 * @return List<Long> 返回结果
	 */
	@SuppressWarnings("unchecked")
	public List<Long> getHouseIdListByBuildingId(Long buildingId,
			Long meterTypeId) {
		if (BlankUtil.isBlank(buildingId) || BlankUtil.isBlank(meterTypeId)) {
			throw new DataAccessLayerException(
					"common:parameters_null_exception, ");
		}

		List<Long> list = new ArrayList<Long>();
		List<BigInteger> tempList = new ArrayList<BigInteger>();
		StringBuilder sb = new StringBuilder(
				"SELECT HMH.HOUSE_ID FROM HM_HOUSE HMH ");
		sb.append("WHERE HMH.BUILDING_ID=? AND HMH.IS_VIRTUAL=0 ");
		sb.append("AND NOT EXISTS (SELECT HMT.HOUSE_ID FROM HM_METER HMT ");
		sb.append("WHERE HMT.HOUSE_ID=HMH.HOUSE_ID AND HMT.METER_TYPE_ID=?) ");

		Query query = super.createSQLQuery(sb.toString(), buildingId,
				meterTypeId);
		if (!BlankUtil.isBlank(query)) {
			tempList = query.list();
		}

		for (BigInteger integer : tempList) {
			list.add(integer.longValue());
		}

		return list;
	}

	/**
	 * 功能描述:根据产权房id查询出其相关的产权房间ID， 如果没有合并，返回其本身，只查“双拼多拼”和“双拼(多拼)产权共有”的合并房
	 * 返回值为id串，如1,2,3<br>
	 *
	 * @param houseId
	 * @return<br>
	 *         Create author:付湘辉<br>
	 *         Create on:2008-10-15<br>
	 *         Edit author:<br>
	 *         Edit on:<br>
	 *         Why:<br>
	 */
	public String getHouseIdsById(Long houseId) {
		if (BlankUtil.isBlank(houseId)) {
			return "";
		}
		SqlConfig sqlConfig = new SqlConfig(HOUSE_QUERY_SQL_CONFIGPATH);
		String sql = sqlConfig.getSql(QUERY_PRORIGHTH_OUSEID_STR_BYID);

		Query query = createSQLQuery(sql.toString(), houseId, houseId);

		return (String) query.uniqueResult();
	}

	/**
	 * 功能描述: 获取指定楼栋下的所有房屋 <br>
	 *
	 * @param buildingId
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-9-8 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	@SuppressWarnings("unchecked")
	public List<FlatDomain> getHouseListByBuildingId(Long buildingId) {
		if (BlankUtil.isBlank(buildingId)) {
			return null;
		}
		String hql = "SELECT hd FROM ProRightDO pro JOIN pro.house hd WHERE hd.building.id=? AND hd.isVirtual=0 AND pro.validity=1 order by hd.building.seqNo";

		List<FlatDomain> list = super.createQuery(hql, buildingId).list();
		return BlankUtil.isBlank(list) ? null : list;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ce.pms.house.dao.IHouseDao#getHouseListByIds(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public List<HouseDomain> getHouseListByIds(String houseIds) {
		String sql = "SELECT NEW HouseDomain(house.keyword,house.buildSize,house.innerSize) FROM HouseDomain house WHERE house.keyword IN (:hid)";
		sql = sql.replace(":hid", houseIds);
		Query query = createQuery(sql);
		return query.list();
	}

	/**
	 * 功能描述: 获取指定楼栋下的所有绑定有效走表以及没有处于退伙中的房屋 <br>
	 *
	 * @param buildingId
	 * @return
	 *
	 * Create author:胡煦 <br>
	 * Create on:2008-10-8 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getHouseListHasByBuildingId(Long buildingId,
			String meterCode) {
		if (BlankUtil.isBlank(buildingId)) {
			return null;
		}

		StringBuffer hql = new StringBuffer(
				"select housedomai1_.HOUSE_ID as PRODUCT1_78_,housedomai1_.BUILDING_ID as BUILDING8_79_,housedomai1_.HOUSE_NUMBER as HOUSE3_79_,housedomai1_3_.STOREY_NUMBER as STOREY3_81_,")
				.append(
						" case when housedomai1_2_.MANAGE_ID is not null then 2 when housedomai1_3_.FLAT_ID is not null then 3 when housedomai1_.HOUSE_ID is not null then 1 end as clazz_")
				.append(
						" from HM_PRO_RIGHT prorightdo0_ inner join HM_HOUSE housedomai1_ on prorightdo0_.HOUSE_ID = housedomai1_.HOUSE_ID inner join HM_PRODUCT housedomai1_1_ on housedomai1_.HOUSE_ID = housedomai1_1_.PRODUCT_ID")
				.append(
						" left outer join HM_MANAGE housedomai1_2_ on housedomai1_.HOUSE_ID = housedomai1_2_.MANAGE_ID left outer join CS_FLAT housedomai1_3_ on housedomai1_.HOUSE_ID = housedomai1_3_.FLAT_ID")
				.append(
						" where (housedomai1_.HOUSE_ID in (select meterdomai2_.HOUSE_ID from HM_METER meterdomai2_, HM_METER_TYPE metertype3_ where meterdomai2_.METER_TYPE_ID = metertype3_.METER_TYPE_ID and meterdomai2_.VALIDITY = 1 and metertype3_.CODE = ?)) and")
				.append(
						" (housedomai1_.HOUSE_ID not in (select gooutdomai4_.HOUSE_ID from HM_GO_OUT gooutdomai4_, SYS_REF_CODE sysrefcode5_ where gooutdomai4_.SYS_REF_ID = sysrefcode5_.SYS_REF_ID and (sysrefcode5_.SYS_REF_VALUE in ('001', '002', '003', '004')))) and")
				.append(
						" housedomai1_.BUILDING_ID = ? and housedomai1_.IS_VIRTUAL = 0 and prorightdo0_.VALIDITY = 1")
				.append(
						" order by housedomai1_3_.STOREY_NUMBER,housedomai1_.HOUSE_NUMBER");

		List<Object[]> list = super.createSQLQuery(hql.toString(),
				new Object[] { meterCode, buildingId }).list();
		return BlankUtil.isBlank(list) ? null : list;
	}

	/**
	 *
	 * 功能描述: 查询所有户型列表
	 *
	 * @author 付湘辉 Created on:2008-6-30
	 *
	 * 修改原因：
	 * @return List<HousePlan> 返回结果
	 */
	@SuppressWarnings("unchecked")
	public List<HousePlan> getHouseModels() {
		List<HousePlan> list = null;

		String queryStr = "FROM HousePlan AS hp ORDER BY hp.code ";

		Query query = super.createQuery(queryStr);

		list = query.list();

		return list;
	}

	/**
	 *
	 * 功能描述: 根据户型id查询户型实体对象
	 *
	 * @author 付湘辉 Created on:2008-7-1
	 *
	 * 修改原因：
	 * @param housePlanId
	 *            户型id
	 * @return HousePlan 返回结果
	 */
	public HousePlan getHousePlanById(Long housePlanId) {
		HousePlan housePlan = null;
		String queryStr = "FROM HousePlan hp WHERE hp.id = ? ";

		Query query = super.createQuery(queryStr, housePlanId);

		housePlan = (HousePlan) query.uniqueResult();

		return housePlan;
	}

	/**
	 * 根据房号查询出房屋ID与业主姓名(个人为业主姓名,企业为企业名称)object[0]=房屋ID;object[1]=房号;object[2]=业主姓名
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getHouseRCustomerInfo(String houseNumber) {
		String hql = "FROM ClientRHouseDO crh left join crh.client.participant participantDO WHERE crh.house.houseNumber=? AND crh.validity='1' AND crh.client.clientType.value = '001'";
		List<Object[]> objList = new ArrayList<Object[]>();
		List<Object[]> list = super.createQuery(hql, houseNumber).list();
		if (list.size() == 0) {
			return null;
		}
		for (Object[] objs : list) {
			Object[] obj = new Object[3];
			ClientRHouseDO crh = (ClientRHouseDO) objs[0];
			obj[0] = crh.getHouse().getKeyword();// 房屋ID
			obj[1] = crh.getHouse().getHouseNumber();// 房号
			if (objs[1] instanceof IndividualDO) {
				IndividualDO individual = (IndividualDO) objs[1];
				obj[2] = individual.getParticipantName();
			} else if (objs[1] instanceof OrganizationDO) {
				OrganizationDO organization = (OrganizationDO) objs[1];
				obj[2] = organization.getParticipantName();
			}
			objList.add(obj);
		}
		return objList;
	}

	/**
	 *
	 * 功能描述: 根据ID查询房间类型
	 *
	 * @author 李昊龙 Created on:2008-10-14
	 *
	 * 修改原因：
	 * @param houseTypeId
	 * @return HouseType 返回结果
	 */
	public HouseType getHouseTypeById(Long houseTypeId) {

		String hql = "select h from HouseType h where h.keyword = :keyword";
		Query query = super.createQuery(hql);
		query.setParameter("keyword", houseTypeId);

		return (HouseType) query.list().get(0);
	}

	/**
	 * 功能描述: 计算指定楼栋的住宅套数 <br>
	 *
	 * @param buildingId
	 * @return Integer
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-7-17 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	public Integer getResiNumber(Long buildingId) {
		Integer result = this.getHouseNumberBy(buildingId,
				SysrefcodeDO.HM_HOUSE_TYPE_CODE_FLAT);
		return result;
	}

	/**
	 * 功能描述: 查询产权房屋，按楼栋编号、单元、楼层、房号从小到大排序 <br>
	 *
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-10-17 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> queryALLHouseSorted() {
		List<Object[]> list = null;
		SqlConfig config = new SqlConfig(HOUSE_QUERY_SQL_CONFIGPATH);
		StringBuffer sql = new StringBuffer(config
				.getSql("queryHouseSortedList"));
		Query query = super.createSQLQuery(sql.toString());
		list = query.list();
		return list;
	}
	
	/**
	 *
	 *
	 * 功能描述: 根据楼栋id查询在这个楼栋下的不同状态的房间的数量列表<br>
	 * 在返回的list中是Object[] [0]：状态value <br>
	 * [1]：状态名称<br>
	 * [2]：状态数量<br>
	 *
	 * @author 付湘辉 Created on:2008-7-3
	 *
	 * 修改原因：
	 * @param buildingId
	 * @return List
	 */
	public List queryDifferentStatusHouseCountBybuildingId(Long buildingId) {
		if (BlankUtil.isBlank(buildingId)) {
			throw new BusinessException("common:parameters_null_exception, ",
					BusinessException.WARNING);
		}

		StringBuilder querySql = new StringBuilder();
		querySql
				.append("SELECT SRC.SYS_REF_VALUE HOUSESTATUSCODE,SRC.SYS_REF_NAME STATUSNAME,");

		// 子查询，查出不同状态的数量
		querySql
				.append(
						"(SELECT COUNT(HHN.HOUSE_ID) FROM HM_HOUSE AS HHN WHERE HHN.BUILDING_ID=")
				.append(buildingId)
				.append(
						" AND HHN.SYS_REF_ID=SRC.SYS_REF_ID AND HHN.IS_VIRTUAL=0) STATUSCOUNT");

		querySql.append(" FROM SYS_REF_CODE SRC");// FROM子句

		querySql.append(" WHERE SRC.SYS_REF_CODE='")// WHERE子句
				.append(SysrefcodeDO.HM_HOUSE_HOUSE_STATUS_CODE).append(
						"' AND SRC.ACTIVE='1' ");

		List list = super.createSQLQuery(querySql.toString()).list();

		return list;
	}

	/**
	 * 功能描述:查询指定所有ID房间列表  <br>
	 * @param houseIds
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2009-1-13  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	@SuppressWarnings("unchecked")
	public List<HouseDomain> queryHouseDomainListByIds(String houseIds) {
		String sql = "FROM HouseDomain house WHERE house.id IN (:hid)";
		sql = sql.replace(":hid", houseIds);
		Query query = createQuery(sql);
		return query.list();
	}

	/**
	 *
	 * 功能描述: 根据房屋搜索VO条件，查询楼栋下房屋图示列表信息 <b>注意：</b>在返回的list中是Object[]<br>
	 * [0]：产权房id <br>
	 * [1]：楼层<br>
	 * [2]：管理房id<br>
	 * [3]：管理房号码<br>
	 * [4]：产权房号码<br>
	 * [5]：房屋状态<br>
	 * [6]：是否在装修中<br>
	 *
	 * @author 付湘辉 Created on:2008-7-3
	 *
	 * 修改原因：
	 * @param searchVO
	 * @return List<Object[]> 返回结果
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> queryHouseIconList(HouseSearchVO searchVO) {

		if (BlankUtil.isBlank(searchVO)
				|| BlankUtil.isBlank(searchVO.getBuildingId())) {
			throw new BusinessException("common:parameters_null_exception, ",
					BusinessException.WARNING);
		}

		List<Object[]> list = new ArrayList<Object[]>();
		StringBuilder coalIdSubQuery = new StringBuilder();
		StringBuilder coalHouseNoSubQuery = new StringBuilder();

		this.generateSubQuerySQL(coalIdSubQuery, coalHouseNoSubQuery);

		// 外层SELECT
		StringBuilder fatherQuery = new StringBuilder();

		fatherQuery.append("SELECT ");
		fatherQuery
				.append("DISTINCT HHN.HOUSE_ID KEYWORD,CF.STOREY_NUMBER FLOOR,");
		fatherQuery.append(coalIdSubQuery);// 管理房id子查询
		fatherQuery.append(coalHouseNoSubQuery);// 管理房号码子查询
		fatherQuery
				.append("HHN.HOUSE_NUMBER AS HOUSENUMBER,SRC.SYS_REF_VALUE AS HOUSESTATUSCODE,");
//		fatherQuery.append("CAST(FLOOR(0.5 +(RAND())) AS CHAR) AS RENOVATION ");// 是否在装修中，现在使用随机数模拟，保留以后增加真实代码
		fatherQuery.append("IF (HHN.SYS_REF_ID1 IS NOT NULL, '1', '0') AS RENOVATION ");
		fatherQuery.append("FROM HM_HOUSE AS HHN ");
		fatherQuery
				.append("JOIN CS_FLAT AS CF ON HHN.HOUSE_ID=CF.FLAT_ID ");// 联接住宅表
		fatherQuery
				.append("LEFT JOIN SYS_REF_CODE AS SRC ON HHN.SYS_REF_ID=SRC.SYS_REF_ID ");// 联接系统参数表

		fatherQuery.append("WHERE 1=1 AND ");

		fatherQuery.append("HHN.BUILDING_ID=").append(searchVO.getBuildingId());// 楼栋id参数
		fatherQuery.append(" AND HHN.IS_VIRTUAL=0");// 是否管理房为0

		// 如果楼栋单元参数不为空
		if (!BlankUtil.isBlank(searchVO.getHouseGrpid())) {
			fatherQuery.append(" AND CF.HOUSE_GRP_ID=").append(
					searchVO.getHouseGrpid());
		} else {
			// 处理房号参数
			fatherQuery.append(dealHouseNumberParam(searchVO));
			// 处理房屋状态参数
			fatherQuery.append(dealHouseStatusParam(searchVO));
		}

		// 排序，楼层降序，房号升序
		fatherQuery
				.append(" ORDER BY CF.STOREY_NUMBER DESC,HHN.HOUSE_NUMBER ASC ");
		// System.out.println("local:=========>:"+fatherQuery.toString());
		list = super.createSQLQuery(fatherQuery.toString()).list();

		return list;
	}
	
	/**
	 *
	 * 功能描述: 根据房屋搜索VO条件，查询楼栋下房屋列表分页信息 注意：在返回的page中resultList中是Object[] <br>
	 * [0]：产权房id <br>
	 * [1]：楼层<br>
	 * [2]：管理房id<br>
	 * [3]：管理房号码<br>
	 * [4]：产权房号码<br>
	 * [5]：产权房户型名称<br>
	 * [6]：建筑面积<br>
	 * [7]：套内面积<br>
	 * [8]：房屋状态<br>
	 * [9]：是否在装修中<br>
	 *
	 * @author 付湘辉 Created on:2008-7-2
	 *
	 * 修改原因：
	 * @param searchVO
	 * @param currentPage
	 * @param pageSize
	 * @return Page 返回结果
	 */
	@AssociatedBuildingQuery(alias = "HHN", property4Building = "BUILDING_ID", beginStr = " AND ", endStr = " ", insertAfterPoint = "WHERE 1=1")
	public Page queryPagedHouses(HouseSearchVO searchVO, Integer currentPage,
			Integer pageSize) {
		Page page = null;

		// 楼栋id参数不能为空
		if (BlankUtil.isBlank(searchVO)
				|| BlankUtil.isBlank(searchVO.getBuildingId())) {
			throw new BusinessException("common:parameters_null_exception, ",
					BusinessException.WARNING);
		}

		StringBuilder coalIdSubQuery = new StringBuilder();
		StringBuilder coalHouseNoSubQuery = new StringBuilder();
		this.generateSubQuerySQL(coalIdSubQuery, coalHouseNoSubQuery);

		// 外层SELECT
		StringBuilder fatherQuery = new StringBuilder();
		fatherQuery.append("SELECT ");
		fatherQuery
				.append("DISTINCT HHN.HOUSE_ID KEYWORD,CF.STOREY_NUMBER FLOOR,");
		fatherQuery.append(coalIdSubQuery);// 管理房id子查询
		fatherQuery.append(coalHouseNoSubQuery);// 管理房号码子查询
		fatherQuery
				.append("HHN.HOUSE_NUMBER AS HOUSENUMBER,HHP.NAME AS HOUSEPLANNAME,HHN.BUILD_SIZE AS BUILDSIZE,HHN.INNER_SIZE AS INNERSIZE,SRC.SYS_REF_NAME AS STATUSNAME,");
//		fatherQuery.append("CAST(FLOOR(0.5 +(RAND())) AS CHAR) AS RENOVATION ");// 是否在装修中，现在使用随机数模拟，保留以后增加真实代码
		fatherQuery.append("IF (HHN.SYS_REF_ID1 IS NOT NULL, '1', '0') AS RENOVATION ");
		fatherQuery.append("FROM HM_HOUSE AS HHN ");
		fatherQuery
				.append("JOIN CS_FLAT AS CF ON HHN.HOUSE_ID = CF.FLAT_ID ");
		fatherQuery
				.append("LEFT JOIN HM_HOUSE_PLAN AS HHP ON HHN.HOUSE_PLAN_ID = HHP.HOUSE_PLAN_ID ");
		fatherQuery
				.append("LEFT JOIN SYS_REF_CODE AS SRC ON HHN.SYS_REF_ID = SRC.SYS_REF_ID ");

		fatherQuery.append("WHERE 1=1 AND ");
		fatherQuery.append("HHN.BUILDING_ID=").append(searchVO.getBuildingId());// 楼栋id参数
		fatherQuery.append(" AND HHN.IS_VIRTUAL=0");// 是否管理房为0

		// 处理房号参数
		fatherQuery.append(dealHouseNumberParam(searchVO));

		// 加入排序字段
		fatherQuery.append(dealOrderBy(searchVO));

		// System.out.println("local:=========>:"+fatherQuery.toString());
		page = super.pagedSqlQueryByPageNo(fatherQuery.toString(), currentPage,
				pageSize);

		return page;
	}

	/**
	 * 功能描述: 处理房号参数 <br>
	 *
	 * @param searchVO
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-8-4 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	private String dealHouseNumberParam(HouseSearchVO searchVO) {
		String houseNumber = searchVO.getHouseNumber();
		if (!BlankUtil.isBlank(houseNumber)) {
			// 转义字符处理
			houseNumber = StringAnalysiUtil
					.toTropicalString(houseNumber, "'_%");
			return " AND HHN.HOUSE_NUMBER LIKE '%" + houseNumber + "%'";
		}
		return "";
	}

	/**
	 * 功能描述: 处理房屋状态参数 <br>
	 *
	 * @param searchVO
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-8-4 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	private String dealHouseStatusParam(HouseSearchVO searchVO) {
		String houseStatusCode = searchVO.getHouseStatusCode();

		// 房间状态参数 如果是"000"表示全部
		if (!BlankUtil.isBlank(houseStatusCode)
				&& !"000".equals(houseStatusCode)) {
			return " AND SRC.SYS_REF_VALUE='" + houseStatusCode + "'";
		}
		return "";
	}

	/**
	 * 功能描述: 得到排序字段 <br>
	 *
	 * @param searchVO
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-8-4 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	private String dealOrderBy(HouseSearchVO searchVO) {
		String sortStr = searchVO.getSortStr();
		StringBuilder orderStr = new StringBuilder(" ORDER BY ");
		if (!BlankUtil.isBlank(sortStr)) {
			String[] sortField = sortStr.split(SysrefcodeDO.CONSTANTS_SPACE);
			if ("houseNumber".equalsIgnoreCase(sortField[0])
					|| "floor".equalsIgnoreCase(sortField[0])) {
				if ("desc".equalsIgnoreCase(sortField[1])) {
					orderStr.append(" CF.STOREY_NUMBER DESC,HHN.HOUSE_NUMBER ");
				} else {
					orderStr.append(" CF.STOREY_NUMBER,HHN.HOUSE_NUMBER ");
				}
			} else {
				orderStr.append(sortField[0].toUpperCase());
				if ("desc".equalsIgnoreCase(sortField[1])) {
					orderStr.append(" DESC ");
				}
			}
		} else {
			// 默认排序
			orderStr.append("CF.STOREY_NUMBER,HHN.HOUSE_NUMBER ");
		}

		return orderStr.toString();
	}

	/**
	 * 功能描述: 拼装子查询SQL <br>
	 *
	 * @param coalIdSubQuery
	 * @param coalHouseNoSubQuery
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-8-4 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	private void generateSubQuerySQL(StringBuilder coalIdSubQuery,
			StringBuilder coalHouseNoSubQuery) {
		final String selectHead = "SELECT ";
		final Character leftBracket = '(';
		final Character rightBracket = ')';

		String selectMid = " FROM HM_HOUSE HHN2 LEFT JOIN HM_REAL_R_VIRTUAL RRV ON HHN2.HOUSE_ID=RRV.VIRTUAL_HOUSE_ID LEFT JOIN HM_MANAGE HMA ON HHN2.HOUSE_ID=HMA.MANAGE_ID WHERE RRV.REAL_HOUSE_ID=HHN.HOUSE_ID AND HMA.VALIDITY=1";
		String coalHouseIdField = "HHN2.HOUSE_ID";// 管理房id字段
		String coalHouseNumberField = "HHN2.HOUSE_NUMBER";// 管理房号码字段
		// 别名
		String coalHouseidAlias = "AS COALITIONHOUSEID";
		String coalHouseNumberAlias = "AS COALITIONNUMBER";

		// 组装管理房id子查询
		coalIdSubQuery.append(leftBracket);
		coalIdSubQuery.append(selectHead);
		coalIdSubQuery.append(coalHouseIdField);
		coalIdSubQuery.append(selectMid);
		coalIdSubQuery.append(rightBracket);
		coalIdSubQuery.append(coalHouseidAlias);
		coalIdSubQuery.append(SysInfo.SEPARATOR_COMMA);

		// 组装管理房号码子查询
		coalHouseNoSubQuery.append(leftBracket);
		coalHouseNoSubQuery.append(selectHead);
		coalHouseNoSubQuery.append(coalHouseNumberField);
		coalHouseNoSubQuery.append(selectMid);
		coalHouseNoSubQuery.append(rightBracket);
		coalHouseNoSubQuery.append(coalHouseNumberAlias);
		coalHouseNoSubQuery.append(SysInfo.SEPARATOR_COMMA);
	}

	/**
	 *
	 * 功能描述: 查询指定楼盘期数下的id不是houseId的房间, 存在返回HouseDomain,否则返回null
	 *
	 * @author 付湘辉 Created on:2008-7-11
	 *
	 * 修改原因：
	 * @param projectId
	 * @param houseId
	 * @param houseNumber
	 * @param virtual
	 *            是否查询管理房 true：是 false：否
	 * @return HouseDomain 返回结果
	 */
	@AssociatedBuildingQuery(alias = "house", beginStr = " ", endStr = " AND ", insertAfterPoint = "HouseDomain house WHERE", property4Building = "building.keyword")
	private HouseDomain getHouseDomainBy(Long projectId, Long houseId,
			String houseNumber, boolean virtual) {

		String houseKeywordStr = "house.id <> ? AND ";
		String projectIdStr = "house.building.project.id=? AND ";
		String hql = "FROM HouseDomain house WHERE :houseKeywordStr :projectIdStr house.houseNumber=? ";

		List<Object> paramList = new ArrayList<Object>();
		int i = 0;
		if (!BlankUtil.isBlank(houseId)) {
			hql = hql.replace(":houseKeywordStr ", houseKeywordStr);
			paramList.add(i++, houseId);
		} else {
			hql = hql.replace(":houseKeywordStr ", "");
		}

		if (!BlankUtil.isBlank(projectId)) {
			hql = hql.replace(":projectIdStr ", projectIdStr);
			paramList.add(i++, projectId);
		} else {
			hql = hql.replace(":projectIdStr ", "");
		}
		paramList.add(i++, houseNumber);

		if (!virtual) {
			hql = hql + "AND house.isVirtual=0 ";
		}

		List list = this.createQuery(hql, paramList.toArray()).list();

		if (BlankUtil.isBlank(list)) {
			return null;
		}

		return (HouseDomain) list.get(0);
	}

	/**
	 * 功能描述: 计算指定楼栋和房屋类型房屋数量 <br>
	 *
	 * @param buildingId
	 * @param houseTypeCode
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-7-17 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	private Integer getHouseNumberBy(Long buildingId, String houseTypeCode) {
		Integer result = 0;
		StringBuffer queryStr = new StringBuffer(
				"SELECT COUNT(*) FROM HM_HOUSE HMH ");
		List<Object> paramList = new ArrayList<Object>();
		int i = 0;
		// 如果房屋类型不为空
		if (!BlankUtil.isBlank(houseTypeCode)) {
			queryStr
					.append("LEFT JOIN HM_HOUSE_PLAN HHP ON HMH.HOUSE_PLAN_ID = HHP.HOUSE_PLAN_ID ");
			queryStr
					.append("LEFT JOIN HM_HOUSE_TYPE HHT ON HHP.HOUSE_TYPE_ID = HHT.HOUSE_TYPE_ID ");
		}
		queryStr.append("WHERE HMH.BUILDING_ID = ? AND HMH.IS_VIRTUAL=0 ");
		paramList.add(i++, buildingId);
		// 如果房屋类型不为空
		if (!BlankUtil.isBlank(houseTypeCode)) {
			queryStr.append("AND HHT.CODE = ? ");
			paramList.add(i++, houseTypeCode);
		}

		Query query = super.createSQLQuery(queryStr.toString(), paramList
				.toArray());
		result = ((BigInteger) query.uniqueResult()).intValue();
		return result;
	}
}
/*
class Conditions {
	public static final String AND = "AND";

	public static final Character EQ = '=';

	public static final Character GT = '>';

	public static final String IS_NOT_NULL = "IS NOT NULL";

	public static final String IS_NULL = "IS NULL";

	public static final String LIKE = "LIKE";

	public static final Character LT = '<';

	public static final String NE = "<>";

	public static final String OR = "OR";

	public static String eq(String value) {
		return Conditions.EQ + value;
	}

	private Conditions condition;
}

class Field {
	*//**
	 * 别名
	 *//*
	private String alias;

	*//**
	 * 字段名称
	 *//*
	private String name;

	*//**
	 * 位置序号
	 *//*
	private int no;

	*//**
	 * @return the name
	 *//*
	public String getName() {
		return name;
	}

	*//**
	 * @param name
	 *            the name to set
	 *//*
	public void setName(String name) {
		this.name = name;
	}
}

class SQLAssembly {
	public static final String ALL = "ALL";

	public static final String AS = "AS";

	public static final String ASC = "ASC";

	public static final String DESC = "DESC";

	public static final String DISTINCT = "DISTINCT";

	public static final Character DQUOTES = '"';

	public static final String FROM = "FROM";

	public static final String GROUP_BY = "GROUP BY";

	public static final String INNER = "INNER";

	public static final String JOIN = "JOIN";

	public static final Character LEFT_BRACKET = '(';// 左括弧

	public static final String LEFT_OUTER = "LEFT OUTER";

	public static final String ON = "ON";

	public static final String ORDER_BY = "ORDER BY";

	public static final Character PERCENTAGE = '%';

	public static final Character POINT = '.';

	public static final Character QUOTES = '\'';

	public static final Character RIGH_TBRACKET = ')';// 右括弧

	public static final String RIGHT_OUTER = "RIGHT OUTER";

	public static final String SELECT_HEAD = "SELECT";

	public static final Character SPACE = ' ';

	public static final Character STAR = '*';

	public static final String WHERE = "WHERE";

	int fieldCount;

	Map<Object, Field> fieldMap = new HashMap<Object, Field>();

	int tableCount;

	Map<Object, Table> tableMap = new HashMap<Object, Table>();

	private Table masterTable;

	public void addConditions(Field field) {
		this.fieldMap.put(fieldCount++, field);
	}

	public void addField(Field field) {
		this.fieldMap.put(fieldCount++, field);
	}

	public void addTable(Table table) {
		if (table.getIsMaster()) {
			masterTable = table;
		}
		this.tableMap.put(tableCount++, table);
	}
}

class Table {
	*//**
	 * 表别名
	 *//*
	private String alias;

	*//**
	 * 是否是主表
	 *//*
	private boolean isMaster;

	*//**
	 * 连接方向
	 *//*
	private String joinDirection;

	*//**
	 * 表名
	 *//*
	private String name;

	*//**
	 * 主键字段
	 *//*
	private Field pkfield;

	*//**
	 * @return the alias
	 *//*
	public String getAlias() {
		return alias;
	}

	*//**
	 * @return the isMaster
	 *//*
	public boolean getIsMaster() {
		return isMaster;
	}

	*//**
	 * @return the joinDirection
	 *//*
	public String getJoinDirection() {
		return joinDirection;
	}

	*//**
	 * @return the name
	 *//*
	public String getName() {
		return name;
	}

	*//**
	 * @param alias
	 *            the alias to set
	 *//*
	public void setAlias(String alias) {
		this.alias = alias;
	}

	*//**
	 * @param isMaster
	 *            the isMaster to set
	 *//*
	public void setIsMaster(boolean isMaster) {
		this.isMaster = isMaster;
	}

	*//**
	 * @param joinDirection
	 *            the joinDirection to set
	 *//*
	public void setJoinDirection(String joinDirection) {
		this.joinDirection = joinDirection;
	}

	*//**
	 * @param name
	 *            the name to set
	 *//*
	public void setName(String name) {
		this.name = name;
	}

	private void generationAlias(String tableName) {
		this.alias = tableName + "_01";
	}
*/

